package org.ocean.online.sys;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.transaction.PlatformTransactionManager;

public class DbGuard {

	JdbcTemplate jt;
	PlatformTransactionManager transactionManager;
	String dbType;
	public DbGuard(DataSource datasource){
		jt =new JdbcTemplate(datasource);
	}
	public List<Map> query(String sql, Object... values){
		List list = jt.queryForList(sql, values);
		return list;
	}
	
	public Map queryForPage(String sql, int pageSize,int pageIndex,Object... values){
		int totalCount = countResult(sql, values);
		int offset = pageSize*(pageIndex-1);
//		String ss = "select top pageSize * from 表名 where id>=(select max(id) from (select top pages id from 表名 order by id asc ) t )";
//		sql = sql +" limit "+pageSize+ " offset "+offset;
		List list = jt.queryForList(sql, values);
		Map result = new HashMap();
		result.put("total", totalCount);
		result.put("rows", list);
		return result;
	}
	
	private int countResult(String sql, Object[] values) {
		Long count = Long.valueOf(0L);

	    sql = "from " + StringUtils.substringAfter(sql, "from");
	    sql = StringUtils.substringBefore(sql, "order by");

	    String countHql = "select count(*) " + sql;
	    return jt.queryForInt(countHql);
	}
//	public String queryForJson(String sql,Object... values){
//		JSONObject jobj = new JSONObject();
//		jobj.put("rows", query(sql,values));
//		return jobj.toString();
//	}
	public void execute(String sql,Object... values) {
		jt.update(sql, values);
	}

	public void insert(String tableName, final Map data) {
		if(!data.containsKey("uid")){
			data.put("uid", UUID.randomUUID().toString());
		}
		StringBuilder cols = new StringBuilder("(");
		StringBuilder vals = new StringBuilder("(");
		final List<String> columns = new ArrayList<String>();
		SqlRowSet rs = jt.queryForRowSet("select * from "+tableName +" where 1=2 ");
		for(String name:rs.getMetaData().getColumnNames()){
			if(data.containsKey(name)){
				cols.append("\"").append(name).append("\"").append(",");
				vals.append("?").append(",");
				columns.add(name);
			}
		}
		cols.replace(cols.length()-1, cols.length(), ")");
		vals.replace(vals.length()-1, vals.length(), ")");
		
		String sql = "insert into "+tableName+cols + " values" + vals;
		jt.execute(sql, new PreparedStatementCallback() {
			
			@Override
			public Object doInPreparedStatement(PreparedStatement ps)
					throws SQLException, DataAccessException {
				
				for(int i=0;i<columns.size();i++){
					ps.setObject(i+1, data.get(columns.get(i)));
				}
				return ps.execute();
			}
		});
	}

	public void delete(String tableName, Map cond) {
		List values = new ArrayList();
		StringBuilder where = new StringBuilder(" where ");
		String sql = "delete from "+tableName+"";
		for(Object key : cond.keySet()){
			where.append(key).append("=").append("?").append(" and ");
			values.add(cond.get(key));
		}
		where.append("1=1");
		jt.update(sql+where, values.toArray());
	}

	public void update(String table, Map newColumns,
			Map cond) {
		StringBuilder set = new StringBuilder(" set ");
		newColumns.remove("uid");
		if(newColumns.isEmpty()){
			return;
		}
		List values = new ArrayList();
		StringBuilder where = new StringBuilder(" where ");
		int index=0;
		for(Object key : newColumns.keySet()){
			set.append(key).append("=").append("?");
			if(index<newColumns.keySet().size()-1){
				set.append(" , ");
			}
			values.add(newColumns.get(key));
			index++;
		}
		
		for(Object key : cond.keySet()){
			where.append(key).append("=").append("?").append(" and ");
			values.add(cond.get(key));
		}
		where.append("1=1");
		String sql = "update "+ table +set + where;
		jt.update(sql, values.toArray());
	}

	public PlatformTransactionManager getTransactionManager() {
		return transactionManager;
	}
	
	public String[] getColumns(String sql){
		List list = new ArrayList();
		jt.setMaxRows(0);
		SqlRowSet result = jt.queryForRowSet(sql);
		return result.getMetaData().getColumnNames();
	}
}
